Apache Doris：高性能分布式分析型数据库技术解析

查看FE的运行状态
show frontends\G;

查看BE的运行状态
show backends\G;

添加follow角色
ALTER SYSTEM ADD FOLLOWER "follower_host:edit_log_port";

添加observer角色
ALTER SYSTEM ADD OBSERVER "observer_host:edit_log_port";

查看集群中每张表的存储占用状态
show data;

查看单张表在集群的存储分布情况
ADMIN SHOW REPLICA DISTRIBUTION FROM db_name.table_name;


显示物化视图
show alter table materialized view from example_db where TableName='table_name';
show alter table rollup;
show alter table materialized view;

确认物化视图(或rollup)已经生效
desc dns_logs_from_kafka all;

删除rollup：
ALTER TABLE db_name.table_name DROP ROLLUP rollup_name;

删除物化视图：
drop materialized view view_name on table_name;

撤销正在创建的rollup(此时数据还在写入rollup)：
CANCEL ALTER TABLE ROLLUP FROM db_name.table_name;

查看某张表的索引情况(创建成功之后)：
show index from table_name;

查看正在创建的(所有表)索引(或修改的字段)：
SHOW ALTER TABLE COLUMN\G;

查看具体某张表的索引(或正在修改的字段)创建情况:
SHOW ALTER TABLE COLUMN WHERE TableName = "table_name"

取消正在创建的索引(或正在修改的字段)：
CANCEL ALTER TABLE COLUMN FROM table_name;

CREATE DATABASE crm;

SHOW chatset;
SHOW COLLATION;

DROP TABLE IF EXISTS `clue_labels`;
CREATE TABLE `clue_labels`
(
    `id`       BIGINT NOT NULL COMMENT "id",
    `clue_sn` largeint NOT NULL COMMENT '线索编码',
    `label_id` INT    NOT NULL COMMENT '标签id'
) UNIQUE KEY(`id`)
  DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
    "replication_allocation" = "tag.location.default: 1",
    "enable_unique_key_merge_on_write" = "true"
);

{
    "label_ids":["7"],
    "query_field":"phone",
    "line_ids":["3"],
    "replay_line_ids":["3","4","9","10"],
    "query_label_ids":["7"],
    "created_at":["1696089600","1703951999"],
    "replay_created_at":["1696089600","1703951999"],
    "page":1,
    "per_page":10
}

SELECT
    `cl`.`id`
  , `cl`.`clue_sn`
  , `cl`.`qq`
  , `cl`.`wechat`
  , `cl`.`phone`
  , `cl`.`deal_start_at`
  , `cl`.`responsible_number`
  , `cl`.`created_at`
  , `cl`.`created_number`
  , `cl`.`signed_at`
  , (
        SELECT
            COUNT(*) AS `total`
        FROM
            `clue_list` `cl2`
        WHERE
              (`cl2`.`data_status` = 1)
          AND (EXISTS (
                          SELECT
                              *
                          FROM
                              `clue_labels` `cll2`
                          WHERE
                                (cll2.`clue_sn` = cl2.`clue_sn`)
                            AND (`cll2`.`label_id` = '33')
                      ))
          AND (`cl2`.`created_at` BETWEEN '1696089600' AND '1703951999')
          AND (EXISTS (
                          SELECT *
                          FROM `clue_order` `clo`
                          WHERE cl2.id = clo.clue_id
                      ))
          AND (cl2.phone = cl.phone AND cl2.phone <> '')
    ) AS `relay_total`
FROM
    `clue_list` `cl`
WHERE
      (`cl`.`data_status` = 1)
  AND (EXISTS (
                  SELECT
                      *
                  FROM
                      `clue_labels` `cll`
                  WHERE
                        (cll.`clue_sn` = cl.`clue_sn`)
                    AND (`cll`.`label_id` = '23')
              ))
  AND (`cl`.`business` = '2-8-1')
  AND (`cl`.`created_at` BETWEEN '1696089600' AND '1703951999')
ORDER BY
    `id` DESC
LIMIT 10 OFFSET 0;

SELECT
    COUNT(DISTINCT cl2.id)                                    AS `replay_total`
  , COUNT(DISTINCT IF(clo.clue_id IS NOT NULL, cl2.id, NULL)) AS `order_clue_total`
  , COUNT(DISTINCT IF(cl2.stage_status = 3, cl2.id, NULL))    AS `sign_clue_total`
FROM
    `clue_list` `cl2`
    LEFT JOIN `clue_order` `clo`
    ON clo.clue_id = cl2.id
WHERE
      (`cl2`.`data_status` = 1)
  AND (EXISTS(
                 SELECT *
                 FROM `clue_labels` `cll2`
                 WHERE (cll2.`clue_sn` = cl2.`clue_sn`) AND (`cll2`.`label_id` = '23')
             ))
  AND (`cl2`.`business` = '1-1-7')
  AND (`cl2`.`created_at` BETWEEN '1704038400' AND '1710345599')
  AND (`cl2`.`wechat` IN (
                             SELECT
                                 `cl`.`wechat`
                             FROM
                                 `clue_list` `cl`
                             WHERE
                                   (`cl`.`data_status` = 1)
                               AND (EXISTS(
                                              SELECT
                                                  *
                                              FROM
                                                  `clue_labels` `cll`
                                              WHERE
                                                    (cll.`clue_sn` = cl.`clue_sn`)
                                                AND (`cll`.`label_id` = '48')
                                          ))
                               AND (`cl`.`business` = '1-1-7')
                               AND (`cl`.`created_at` BETWEEN '1704038400' AND '1710345599')
                               AND (`cl`.`wechat` <> '')
                         ))